package com.titan.core.utils.dbinfo.impl;

import java.sql.Connection;

import com.titan.core.utils.data.StringUtils;
import com.titan.core.utils.db.DBUtils;
import com.titan.core.utils.db.RecordSet;
import com.titan.core.utils.dbinfo.interfaces.TableInfo;


/**
 * @author 周成功
 * @email 86898837@qq.com
 * @date 2020年02月20日
 */
public class TableInfoMysqlImpl implements TableInfo {
    // {列英文名, 数据类型, 数据长度, 数值指定长度(整数+小数), 数值小数部分长度, 是否可以为null(Y/N), 列注释}
    public String[][] getColumnInfo(Connection conn, String tablename) throws Exception {
        return getColumnInfo(conn, null, tablename);
    }
    public String[][] getColumnInfo(Connection conn, String schema, String tablename) throws Exception {
        StringBuffer sql = new StringBuffer(1024);
        sql.append("select column_name");
        sql.append("       ,data_type");
        sql.append("       ,character_maximum_length");
        sql.append("       ,numeric_precision");
        sql.append("       ,numeric_scale");
        sql.append("       ,is_nullable");
        sql.append("       ,column_comment");
        sql.append("  from information_schema.columns");
        sql.append(" where upper(table_name)=upper(?)");
        if(StringUtils.isNotEmpty(schema)) {
            sql.append(" and upper(table_schema) = upper('"+schema+"')");
        }
        sql.append(" order by ordinal_position");
        RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql.toString(), new String[]{tablename});
        String[][] result =new String[rest.getRowCount()][7];
        for (int row = 0; row < rest.getRowCount(); row++) {
            result[row][0] =rest.getString(row + 1, "column_name");
            result[row][1] =rest.getString(row + 1, "data_type");
            result[row][2] =Integer.toString(rest.getInt(row + 1, "character_maximum_length"));
            result[row][3] =Integer.toString(rest.getInt(row + 1, "numeric_precision"));
            result[row][4] =Integer.toString(rest.getInt(row + 1, "numeric_scale"));
            result[row][5] =rest.getString(row + 1, "is_nullable");
            result[row][6] =rest.getString(row + 1, "column_comment");
//            if("upload_data".equalsIgnoreCase(result[row][0])) {
//                System.out.println(FormatUtils.toString(result[row]));
//            }
        }
        return result;
    }
    
    
    public String[] getPrimaryKey(Connection conn, String tablename) throws Exception {
        return getPrimaryKey(conn, null, tablename);
    }
    public String[] getPrimaryKey(Connection conn, String schema, String tablename) throws Exception {
        StringBuffer sql = new StringBuffer(1024);
        sql.append("select column_name");
        sql.append("  from information_schema.key_column_usage");
        sql.append(" where upper(constraint_name)='PRIMARY'");
        sql.append("   and upper(table_name)=upper(?)");
        if(StringUtils.isNotEmpty(schema)) {
            sql.append(" and upper(table_schema) = upper('"+schema+"')");
        }
        sql.append(" order by ordinal_position");
        RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql.toString(), new String[]{tablename});
        String[] primaryKey = new String[rest.getRowCount()];
        for (int i = 0; i < rest.getRowCount(); i++) {
            primaryKey[i] = rest.getString(i + 1, "column_name");
        }
        return primaryKey;
    }
    
    
    // 表类型, 表注释
    public String[] getTableInfo(Connection conn, String tablename) throws Exception {
        return getTableInfo(conn, null, tablename);
    }
    public String[] getTableInfo(Connection conn, String schema, String tablename) throws Exception {
        // 获得表信息
        StringBuffer sql = new StringBuffer(1024);
        sql.append("select table_type, table_comment");
        sql.append("  from information_schema.tables");
        sql.append(" where upper(table_name)=upper(?)");
        if(StringUtils.isNotEmpty(schema)) {
            sql.append(" and upper(table_schema) = upper('"+schema+"')");
        }
        RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql.toString(), new String[]{tablename});
        if (rest.moveNext()) {
            return new String[]{rest.getString("table_type"), rest.getString("table_comment")};
        } else {
            return null;
        }
    }
    @Override
    public String[][] getUniqueIndex(Connection conn, String tablename) throws Exception {
        return getUniqueIndex(conn, null, tablename);
    }
    @Override
    public String[][] getUniqueIndex(Connection conn, String schema, String tablename) throws Exception {
        StringBuffer sql = new StringBuffer(1024);
        sql.append("select distinct constraint_name");
        sql.append("  from information_schema.key_column_usage");
        sql.append(" where upper(constraint_name)!='PRIMARY'");
        sql.append("   and upper(table_name)=upper(?)");
        if(StringUtils.isNotEmpty(schema)) {
            sql.append(" and upper(table_schema) = upper('"+schema+"')");
        }
        String[] values = DBUtils.getValues(conn, sql.toString(), new String[]{tablename});
        String[][] items = new String[values.length][];
        for (int i = 0; i < items.length; i++) {
            sql = new StringBuffer(1024);
            sql.append("select constraint_name,column_name");
            sql.append("  from information_schema.key_column_usage");
            sql.append(" where upper(constraint_name)!='PRIMARY'");
            sql.append("   and upper(table_name)=upper(?)");
            sql.append("   and upper(constraint_name)=upper(?)");
            if(StringUtils.isNotEmpty(schema)) {
                sql.append(" and upper(table_schema) = upper('"+schema+"')");
            }
            sql.append(" order by ordinal_position");
            RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql.toString(), new String[]{tablename, values[i]});
            String[] primaryKey = new String[rest.getRowCount()];
            for (int j = 0; j < rest.getRowCount(); j++) {
                primaryKey[j] = rest.getString(j + 1, "column_name");
            }
            items[i] = primaryKey;
        }
        return items;
    }

}

